"""
Initial migration - create all core tables

Revision ID: 001
Revises: 
Create Date: 2025-09-19
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '001'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 创建用户表
    op.create_table('users',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('email', sa.String(length=255), nullable=False),
        sa.Column('username', sa.String(length=50), nullable=False),
        sa.Column('full_name', sa.String(length=100), nullable=True),
        sa.Column('hashed_password', sa.String(length=255), nullable=False),
        sa.Column('role', sa.String(length=20), nullable=False),
        sa.Column('status', sa.String(length=20), nullable=False),
        sa.Column('avatar_url', sa.String(length=500), nullable=True),
        sa.Column('bio', sa.Text(), nullable=True),
        sa.Column('daily_generation_limit', sa.Integer(), nullable=True),
        sa.Column('storage_quota_mb', sa.Integer(), nullable=True),
        sa.Column('used_storage_mb', sa.Integer(), nullable=True),
        sa.Column('email_verified', sa.Boolean(), nullable=True),
        sa.Column('two_factor_enabled', sa.Boolean(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.Column('last_login_at', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
        sa.UniqueConstraint('username')
    )
    op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=False)
    op.create_index(op.f('ix_users_status'), 'users', ['status'], unique=False)
    op.create_index(op.f('ix_users_username'), 'users', ['username'], unique=False)

    # 创建用户设置表
    op.create_table('user_settings',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('theme', sa.String(length=20), nullable=True),
        sa.Column('language', sa.String(length=10), nullable=True),
        sa.Column('timezone', sa.String(length=50), nullable=True),
        sa.Column('notifications_enabled', sa.Boolean(), nullable=True),
        sa.Column('email_notifications', sa.Boolean(), nullable=True),
        sa.Column('auto_save_drafts', sa.Boolean(), nullable=True),
        sa.Column('max_concurrent_tasks', sa.Integer(), nullable=True),
        sa.Column('default_output_format', sa.String(length=20), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_user_settings_user_id'), 'user_settings', ['user_id'], unique=False)

    # 创建文件夹表
    op.create_table('folders',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('parent_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('path', sa.String(length=1000), nullable=False),
        sa.Column('is_public', sa.Boolean(), nullable=True),
        sa.Column('sort_order', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['parent_id'], ['folders.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_folders_parent_id'), 'folders', ['parent_id'], unique=False)
    op.create_index(op.f('ix_folders_user_id'), 'folders', ['user_id'], unique=False)

    # 创建标签表
    op.create_table('tags',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('name', sa.String(length=50), nullable=False),
        sa.Column('color', sa.String(length=7), nullable=True),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name', 'user_id', name='uq_tags_name_user_id')
    )
    op.create_index(op.f('ix_tags_name'), 'tags', ['name'], unique=False)
    op.create_index(op.f('ix_tags_user_id'), 'tags', ['user_id'], unique=False)

    # 创建生成内容表
    op.create_table('generated_contents',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('folder_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('title', sa.String(length=200), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('content_type', sa.String(length=50), nullable=False),
        sa.Column('file_path', sa.String(length=500), nullable=True),
        sa.Column('file_size', sa.Integer(), nullable=True),
        sa.Column('duration', sa.Integer(), nullable=True),
        sa.Column('width', sa.Integer(), nullable=True),
        sa.Column('height', sa.Integer(), nullable=True),
        sa.Column('metadata', sa.JSON(), nullable=True),
        sa.Column('status', sa.String(length=20), nullable=False),
        sa.Column('processing_time_ms', sa.Integer(), nullable=True),
        sa.Column('generation_params', sa.JSON(), nullable=True),
        sa.Column('model_used', sa.String(length=100), nullable=True),
        sa.Column('cost', sa.Integer(), nullable=True),
        sa.Column('is_public', sa.Boolean(), nullable=True),
        sa.Column('like_count', sa.Integer(), nullable=True),
        sa.Column('view_count', sa.Integer(), nullable=True),
        sa.Column('download_count', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['folder_id'], ['folders.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_generated_contents_content_type'), 'generated_contents', ['content_type'], unique=False)
    op.create_index(op.f('ix_generated_contents_folder_id'), 'generated_contents', ['folder_id'], unique=False)
    op.create_index(op.f('ix_generated_contents_status'), 'generated_contents', ['status'], unique=False)
    op.create_index(op.f('ix_generated_contents_user_id'), 'generated_contents', ['user_id'], unique=False)

    # 创建内容标签关联表
    op.create_table('content_tag',
        sa.Column('content_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('tag_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.ForeignKeyConstraint(['content_id'], ['generated_contents.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['tag_id'], ['tags.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('content_id', 'tag_id')
    )

    # 创建创作请求表
    op.create_table('creation_requests',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('folder_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('title', sa.String(length=200), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('content_type', sa.String(length=50), nullable=False),
        sa.Column('prompt_text', sa.Text(), nullable=True),
        sa.Column('prompt_image_url', sa.String(length=500), nullable=True),
        sa.Column('prompt_audio_url', sa.String(length=500), nullable=True),
        sa.Column('prompt_video_url', sa.String(length=500), nullable=True),
        sa.Column('parameters', sa.JSON(), nullable=True),
        sa.Column('model_preference', sa.String(length=100), nullable=True),
        sa.Column('status', sa.String(length=20), nullable=False),
        sa.Column('priority', sa.Integer(), nullable=True),
        sa.Column('scheduled_time', sa.DateTime(), nullable=True),
        sa.Column('completion_deadline', sa.DateTime(), nullable=True),
        sa.Column('estimated_cost', sa.Integer(), nullable=True),
        sa.Column('actual_cost', sa.Integer(), nullable=True),
        sa.Column('result_content_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('retry_count', sa.Integer(), nullable=True),
        sa.Column('max_retries', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['folder_id'], ['folders.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['result_content_id'], ['generated_contents.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_creation_requests_content_type'), 'creation_requests', ['content_type'], unique=False)
    op.create_index(op.f('ix_creation_requests_folder_id'), 'creation_requests', ['folder_id'], unique=False)
    op.create_index(op.f('ix_creation_requests_result_content_id'), 'creation_requests', ['result_content_id'], unique=False)
    op.create_index(op.f('ix_creation_requests_status'), 'creation_requests', ['status'], unique=False)
    op.create_index(op.f('ix_creation_requests_user_id'), 'creation_requests', ['user_id'], unique=False)

    # 创建任务队列表
    op.create_table('task_queues',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('related_content_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('related_request_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('task_type', sa.String(length=50), nullable=False),
        sa.Column('task_name', sa.String(length=200), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('task_id', sa.String(length=100), nullable=False),
        sa.Column('parent_task_id', sa.String(length=100), nullable=True),
        sa.Column('status', sa.String(length=20), nullable=False),
        sa.Column('priority', sa.Integer(), nullable=False),
        sa.Column('progress', sa.Integer(), nullable=True),
        sa.Column('progress_message', sa.String(length=500), nullable=True),
        sa.Column('current_step', sa.String(length=100), nullable=True),
        sa.Column('total_steps', sa.Integer(), nullable=True),
        sa.Column('completed_steps', sa.Integer(), nullable=True),
        sa.Column('retry_count', sa.Integer(), nullable=True),
        sa.Column('max_retries', sa.Integer(), nullable=True),
        sa.Column('task_parameters', sa.JSON(), nullable=True),
        sa.Column('task_metadata', sa.JSON(), nullable=True),
        sa.Column('worker_id', sa.String(length=100), nullable=True),
        sa.Column('worker_type', sa.String(length=50), nullable=True),
        sa.Column('execution_host', sa.String(length=100), nullable=True),
        sa.Column('execution_pid', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.Column('queued_at', sa.DateTime(), nullable=True),
        sa.Column('started_at', sa.DateTime(), nullable=True),
        sa.Column('completed_at', sa.DateTime(), nullable=True),
        sa.Column('timeout_seconds', sa.Integer(), nullable=True),
        sa.Column('estimated_duration', sa.Integer(), nullable=True),
        sa.Column('result_data', sa.JSON(), nullable=True),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('error_code', sa.String(length=50), nullable=True),
        sa.Column('error_details', sa.JSON(), nullable=True),
        sa.Column('memory_usage_mb', sa.Integer(), nullable=True),
        sa.Column('cpu_usage_percent', sa.Integer(), nullable=True),
        sa.Column('estimated_cost', sa.Integer(), nullable=True),
        sa.Column('actual_cost', sa.Integer(), nullable=True),
        sa.Column('depends_on_task_ids', sa.JSON(), nullable=True),
        sa.Column('scheduled_at', sa.DateTime(), nullable=True),
        sa.Column('cron_expression', sa.String(length=100), nullable=True),
        sa.Column('expires_at', sa.DateTime(), nullable=True),
        sa.Column('retention_days', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['related_content_id'], ['generated_contents.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['related_request_id'], ['creation_requests.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('task_id')
    )
    op.create_index(op.f('ix_task_queues_parent_task_id'), 'task_queues', ['parent_task_id'], unique=False)
    op.create_index(op.f('ix_task_queues_related_content_id'), 'task_queues', ['related_content_id'], unique=False)
    op.create_index(op.f('ix_task_queues_related_request_id'), 'task_queues', ['related_request_id'], unique=False)
    op.create_index(op.f('ix_task_queues_status'), 'task_queues', ['status'], unique=False)
    op.create_index(op.f('ix_task_queues_task_id'), 'task_queues', ['task_id'], unique=False)
    op.create_index(op.f('ix_task_queues_task_type'), 'task_queues', ['task_type'], unique=False)
    op.create_index(op.f('ix_task_queues_user_id'), 'task_queues', ['user_id'], unique=False)
    op.create_index(op.f('ix_task_queues_worker_id'), 'task_queues', ['worker_id'], unique=False)

    # 创建系统日志表
    op.create_table('system_logs',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('log_level', sa.String(length=20), nullable=False),
        sa.Column('module', sa.String(length=100), nullable=False),
        sa.Column('action', sa.String(length=100), nullable=False),
        sa.Column('message', sa.Text(), nullable=False),
        sa.Column('details', sa.JSON(), nullable=True),
        sa.Column('ip_address', sa.String(length=45), nullable=True),
        sa.Column('user_agent', sa.String(length=500), nullable=True),
        sa.Column('request_id', sa.String(length=100), nullable=True),
        sa.Column('session_id', sa.String(length=100), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='SET NULL'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_system_logs_action'), 'system_logs', ['action'], unique=False)
    op.create_index(op.f('ix_system_logs_log_level'), 'system_logs', ['log_level'], unique=False)
    op.create_index(op.f('ix_system_logs_module'), 'system_logs', ['module'], unique=False)
    op.create_index(op.f('ix_system_logs_user_id'), 'system_logs', ['user_id'], unique=False)


def downgrade() -> None:
    # 删除内容标签关联表
    op.drop_table('content_tag_associations')
    
    # 删除生成内容表
    op.drop_index(op.f('ix_generated_contents_user_id'), table_name='generated_contents')
    op.drop_index(op.f('ix_generated_contents_status'), table_name='generated_contents')
    op.drop_index(op.f('ix_generated_contents_format'), table_name='generated_contents')
    op.drop_index(op.f('ix_generated_contents_folder_id'), table_name='generated_contents')
    op.drop_index(op.f('ix_generated_contents_creation_request_id'), table_name='generated_contents')
    op.drop_index(op.f('ix_generated_contents_content_type'), table_name='generated_contents')
    op.drop_table('generated_contents')
    
    # 删除系统日志表
    op.drop_index(op.f('ix_system_logs_user_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_trace_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_timestamp'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_session_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_request_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_related_task_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_related_request_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_related_content_id'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_level'), table_name='system_logs')
    op.drop_index(op.f('ix_system_logs_category'), table_name='system_logs')
    op.drop_table('system_logs')
    
    # 删除任务队列表
    op.drop_index(op.f('ix_task_queues_status'), table_name='task_queues')
    op.drop_index(op.f('ix_task_queues_related_request_id'), table_name='task_queues')
    op.drop_index(op.f('ix_task_queues_related_content_id'), table_name='task_queues')
    op.drop_index(op.f('ix_task_queues_priority'), table_name='task_queues')
    op.drop_table('task_queues')
    
    # 删除内容标签表
    op.drop_index(op.f('ix_content_tags_name'), table_name='content_tags')
    op.drop_index(op.f('ix_content_tags_category'), table_name='content_tags')
    op.drop_table('content_tags')
    
    # 删除文件夹表
    op.drop_index(op.f('ix_folders_user_id'), table_name='folders')
    op.drop_index(op.f('ix_folders_path'), table_name='folders')
    op.drop_index(op.f('ix_folders_parent_id'), table_name='folders')
    op.drop_index(op.f('ix_folders_is_deleted'), table_name='folders')
    op.drop_index(op.f('ix_folders_folder_type'), table_name='folders')
    op.drop_table('folders')
    
    # 删除创作请求表
    op.drop_index(op.f('ix_creation_requests_user_id'), table_name='creation_requests')
    op.drop_index(op.f('ix_creation_requests_task_id'), table_name='creation_requests')
    op.drop_index(op.f('ix_creation_requests_status'), table_name='creation_requests')
    op.drop_index(op.f('ix_creation_requests_priority'), table_name='creation_requests')
    op.drop_index(op.f('ix_creation_requests_content_type'), table_name='creation_requests')
    op.drop_table('creation_requests')
    
    # 删除用户设置表
    op.drop_index(op.f('ix_user_settings_user_id'), table_name='user_settings')
    op.drop_table('user_settings')
    
    # 删除用户表
    op.drop_index(op.f('ix_users_username'), table_name='users')
    op.drop_index(op.f('ix_users_status'), table_name='users')
    op.drop_index(op.f('ix_users_email'), table_name='users')
    op.drop_table('users')